/***************************************************************
*  Copyright (c) 2007 by GroupMe! Team (www.groupme.net)
*  All rights reserved.
*
*  This file is part of the GroupMe! Project. Source code of 
*  this project is closed and redistribution of this code is
*  prohibited. 
*  
*  Contact: http://www.groupme.net
*
*  This copyright notice MUST APPEAR in all copies of the file!
***************************************************************/
package net.groupme.search.strategy;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.List;

import net.groupme.model.Group;
import net.groupme.model.ObjectFactory;
import net.groupme.model.Resource;
import net.groupme.model.User;
import net.groupme.model.dao.DATABASE_CONSTANTS;
import net.groupme.search.strategy.model.WeightedIdentifiableComparatorByWeight;
import net.groupme.search.strategy.model.WeightedTag;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * created on Jul 6, 2007
 * 
 * Implementation of the {@link TagSearchStrategy} which focuses on the amount of tags in order 
 * to rank tags in context of a search.
 * 
 * @author Fabian Abel, <a href="mailto:abel@l3s.de">abel@l3s.de</a>
 * @author last edited by: $Author: fabian $
 * 
 * @version $Revision: 1.10 $ $Date: 2009-01-20 18:47:45 $
 */
public class TagSearchStrategyAmount implements TagSearchStrategy {
	/** Logger for this class and subclasses */
	protected final static  Log logger = LogFactory.getLog(TagSearchStrategyAmount.class);
	
	/** when computing the tag cloud of a group: weight for group tags */
	public static final Double WEIGHT_OF_GROUP_TAGS = 3.0;
	
	/** when computing the tag cloud of a group:  weight for resource tags that are assigned within the group  */
	public static final Double WEIGHT_OF_RESOURCE_TAGS_WITHIN_GROUP = 2.0;
	
	/** when computing the tag cloud of a group:  weight for resource tags that are not assigned within the group  */
	public static final Double WEIGHT_OF_RESOURCE_TAGS_IN_OTHER_GROUPS = 1.0;

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.TagSearchStrategy#getTagCloud(java.util.Calendar, java.util.Calendar, net.groupme.model.User, java.lang.Integer)
	 */
	public List<WeightedTag> getTagCloud(Calendar beginOfPeriod, Calendar endOfPeriod,
			User user, Integer topK) {
		//convert to sql-Date:
		Date from = null;
		Date to = null;
		if(beginOfPeriod != null){
			from = new Date(beginOfPeriod.getTimeInMillis());
		}
		if(endOfPeriod != null){
			to = new Date(endOfPeriod.getTimeInMillis());
		}
		
		//construct query:
		String query = "SELECT tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ", t." + DATABASE_CONSTANTS.TAG_KEYWORD + ", t." +  DATABASE_CONSTANTS.TAG_DESCRIPTION + ",  count(tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ") " +
				" FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " as tas, " + DATABASE_CONSTANTS.TABLE_TAG + " as t " +
				" WHERE tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = t." + DATABASE_CONSTANTS.TAG_ID + " " ;		
		if(user != null){
			query += " AND tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_USERID + " = '" + user.getId() + "' ";
		}
		if(from != null){
			query += " AND tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_DATE + " >= '" + from + "' ";
		}
		if(to != null){
			query += " AND tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_DATE + " <= '" + to + "' ";
		}
		query +=  " group by tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " order by count(tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ") DESC";
		
		
		Statement sqlStatement = null;
		ResultSet tagsAndAmount = null;
		List<WeightedTag> cloud = new ArrayList<WeightedTag>();
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			tagsAndAmount = sqlStatement.executeQuery(query);
			while(tagsAndAmount.next()){
				WeightedTag wt = new WeightedTag(ObjectFactory.getTagByIdKeyWordAndDescription(tagsAndAmount.getString(1), tagsAndAmount.getString(2), tagsAndAmount.getString(3)), (WEIGHT_OF_GROUP_TAGS * tagsAndAmount.getDouble(4)));
				cloud.add(wt);
			}
		} catch (SQLException e) {
			logger.error("Unable to connect to DB while querying for Tag Cloud.", e);
		}
		Collections.sort(cloud, new WeightedIdentifiableComparatorByWeight());
		if(topK != null && (topK < cloud.size())){
			return cloud.subList(0, topK);
		}
		return cloud;
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.TagSearchStrategy#getTagCloud(net.groupme.model.Group, java.lang.Integer)
	 */
	public List<WeightedTag> getTagCloud(Group group, Integer topK) {
		//Tags, die der Gruppe assigned sind:
		String groupQuery = "SELECT " +
								" tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ", " +
								" t." + DATABASE_CONSTANTS.TAG_KEYWORD + "," +
								" t." +  DATABASE_CONSTANTS.TAG_DESCRIPTION + ", " +
								" count(tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ") " +
							" FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " as tas, " + DATABASE_CONSTANTS.TABLE_TAG + " as t " + 
							" WHERE tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = t." + DATABASE_CONSTANTS.TAG_ID + " AND " 
									+ " tas." +  DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " = '" + group.getId() + "' group by tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID;
		//Tags, die Ressourcen innerhalb der Gruppe assigned sind:
		String groupMemberQuery = "SELECT " +
								" tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ", " +
								" t." + DATABASE_CONSTANTS.TAG_KEYWORD + "," +
								" t." +  DATABASE_CONSTANTS.TAG_DESCRIPTION + ", " +
								" count(tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ") " +
							" FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " as tas, " + DATABASE_CONSTANTS.TABLE_TAG + " as t " + 
							" WHERE tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = t." + DATABASE_CONSTANTS.TAG_ID + " AND " 
									+ " tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_GROUPID + " = " + group.getId() + " AND " 
									+ " tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " in (SELECT " + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID +" FROM " + DATABASE_CONSTANTS.TABLE_GROUPRESOURCE + " WHERE " + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + " = '" + group.getId() + "') group by tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID;
		//Tags, die Ressourcen der Gruppe in anderen Gruppen assigned wurden:
		String groupMemberOtherQuery = "SELECT " +
								" tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ", " +
								" t." + DATABASE_CONSTANTS.TAG_KEYWORD + "," +
								" t." +  DATABASE_CONSTANTS.TAG_DESCRIPTION + ", " +
								" count(tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ") " +
							" FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " as tas, " + DATABASE_CONSTANTS.TABLE_TAG + " as t " + 
							" WHERE tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = t." + DATABASE_CONSTANTS.TAG_ID + " AND " 
								+ " tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_GROUPID + " != " + group.getId() + " AND " 
								+ " tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " in (SELECT " + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID +" FROM " + DATABASE_CONSTANTS.TABLE_GROUPRESOURCE + " WHERE " + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + " = '" + group.getId() + "') group by tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID;
		
		Statement sqlStatement = null;
		ResultSet tagsAndAmount = null;
		List<WeightedTag> cloud = new ArrayList<WeightedTag>();
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			
//			Tags, die der Gruppe assigned sind:
			tagsAndAmount = sqlStatement.executeQuery(groupQuery);
			while(tagsAndAmount.next()){
				WeightedTag wt = new WeightedTag(ObjectFactory.getTagByIdKeyWordAndDescription(tagsAndAmount.getString(1), tagsAndAmount.getString(2), tagsAndAmount.getString(3)), (WEIGHT_OF_GROUP_TAGS * tagsAndAmount.getDouble(4)));
				cloud.add(wt);
			}

//			Tags, die Ressourcen innerhalb der Gruppe assigned sind:
			tagsAndAmount = sqlStatement.executeQuery(groupMemberQuery);
			while(tagsAndAmount.next()){
				WeightedTag wt = new WeightedTag(ObjectFactory.getTagByIdKeyWordAndDescription(tagsAndAmount.getString(1), tagsAndAmount.getString(2), tagsAndAmount.getString(3)), (WEIGHT_OF_RESOURCE_TAGS_WITHIN_GROUP * tagsAndAmount.getDouble(4)));
				if(cloud.contains(wt)){
					cloud.get(cloud.indexOf(wt)).weight += (WEIGHT_OF_RESOURCE_TAGS_WITHIN_GROUP * tagsAndAmount.getDouble(4));
				}else{
					cloud.add(wt);
				}
			}
			
//			Tags, die Ressourcen der Gruppe in anderen Gruppen assigned wurden:
			tagsAndAmount = sqlStatement.executeQuery(groupMemberOtherQuery);
			while(tagsAndAmount.next()){
				WeightedTag wt = new WeightedTag(ObjectFactory.getTagByIdKeyWordAndDescription(tagsAndAmount.getString(1), tagsAndAmount.getString(2), tagsAndAmount.getString(3)), (WEIGHT_OF_RESOURCE_TAGS_IN_OTHER_GROUPS * tagsAndAmount.getDouble(4)));
				if(cloud.contains(wt)){
					cloud.get(cloud.indexOf(wt)).weight += (WEIGHT_OF_RESOURCE_TAGS_IN_OTHER_GROUPS * tagsAndAmount.getDouble(4));
				}else{
					cloud.add(wt);
				}
			}
		} catch (SQLException e) {
			logger.error("Unable to connect to DB while querying for Tag Cloud.", e);
		}
		/*	
		TagQueryingUtility tagInquirer = new TagQueryingUtility();
		List<WeightedTag> cloud = new ArrayList<WeightedTag>();
		for(Tag tag: tagInquirer.getAllTagAssignmentsOfResource(group)){
			WeightedTag wt = new WeightedTag(tag, WEIGHT_OF_GROUP_TAGS);
			if(cloud.contains(wt)){
				cloud.get(cloud.indexOf(wt)).weight += WEIGHT_OF_GROUP_TAGS;
			}else{
				cloud.add(wt);
			}
		}
		Collection<Tag> tags = null;
		WeightedTag wt = null;
		int pos = -1;
		for(Resource res: group.getResources()){
			tags = tagInquirer.getAllTagAssignmentsOfResource(res, group);
			for(Tag tag: tagInquirer.getAllTagAssignmentsOfResource(res)){
				if(tags.contains(tag)){
					wt = new WeightedTag(tag, WEIGHT_OF_RESOURCE_TAGS_WITHIN_GROUP);
				}else{
					wt = new WeightedTag(tag, WEIGHT_OF_RESOURCE_TAGS_IN_OTHER_GROUPS);
				}
				if(cloud.contains(wt)){
					pos = cloud.indexOf(wt);
					cloud.get(pos).weight = cloud.get(pos).weight + wt.weight;
				}else{
					cloud.add(wt);
				}
			}
		}
		Collections.sort(cloud, new WeightedIdentifiableComparatorByWeight());
		if(topK != null && (topK < cloud.size())){
			return cloud.subList(0, topK);
		}*/
		Collections.sort(cloud, new WeightedIdentifiableComparatorByWeight());
		if(topK != null && (topK < cloud.size())){
			return cloud.subList(0, topK);
		}
		return cloud;
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.TagSearchStrategy#getTagCloud(net.groupme.model.Resource, java.lang.Integer)
	 */
	public List<WeightedTag> getTagCloud(Resource resource, Integer topK) {
		String query = "SELECT " +
				"t."+ DATABASE_CONSTANTS.TAG_ID +", t."+ DATABASE_CONSTANTS.TAG_KEYWORD + ", count(*) as n " +
				"FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE   + " tas, " 
						+ DATABASE_CONSTANTS.TABLE_TAG + " t " +
				"WHERE t."+ DATABASE_CONSTANTS.TAG_ID +" = tas."+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + 
				" AND tas."+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID +" = " + resource.getId() + 
				" group by tas."+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID +" order by n desc " + (topK != null ? ("limit " + topK) : "");
		Statement sqlStatement = null;
		ResultSet tagsAndAmount = null;
		List<WeightedTag> cloud = new ArrayList<WeightedTag>();
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			
//			Tags, die der Resource assigned sind:
			tagsAndAmount = sqlStatement.executeQuery(query);
			while(tagsAndAmount.next()){
				WeightedTag wt = new WeightedTag(ObjectFactory.getTagByIdKeyWordAndDescription(tagsAndAmount.getString(1), tagsAndAmount.getString(2), tagsAndAmount.getString(2)),  tagsAndAmount.getDouble(3));
				cloud.add(wt);
			}
		} catch (SQLException e) {
			logger.error("Problem while querying DB for Tag Cloud of a resource.", e);
		}
		return cloud;
	}
}
